import re
import glob
import pymysql

from db_setting import *



class ExecuteSqlScript:
    '''
    def execute_script(self, sql_script):

        传入一个指定的sql脚本并执行
    
    def execute_all_script(self):

        执行所build_db文件下的sql脚本

    '''
    def __init__(self, clear_db=False):
        '''
        :clear_db: bool 当该值为True时将原有的数据库删除
        '''

        self.creat_datebase(clear_db)# 测试并创建MASTER_DB_USER数据库
        self.conn = pymysql.connect(
            host = MASTER_DB_HOST,
            port = MASTER_DB_PORT,
            database = MASTER_DB_NAME,
            user = MASTER_DB_USER,
            password = MASTER_DB_PASS_WORD
            
        )
        self.cursor = self.conn.cursor()


    def creat_datebase(self, clear_db=False):
        '''
        测试并创建MASTER_DB_USER数据库
        '''
        conn = pymysql.connect(
            host = MASTER_DB_HOST,
            port = MASTER_DB_PORT,
            user = MASTER_DB_USER,
            password = MASTER_DB_PASS_WORD  
        )
        cursor = conn.cursor()

        if clear_db == True: #清除原有数据库存，用来初始化项目
            drop_sql = f'DROP DATABASE IF EXISTS {MASTER_DB_NAME};'
            cursor.execute(drop_sql) 
        sql = f'CREATE DATABASE IF NOT EXISTS {MASTER_DB_NAME} DEFAULT CHARSET utf8 COLLATE utf8_general_ci;'
        cursor.execute(sql)
        print(sql)
        conn.commit()
        cursor.close()
        conn.close()

    def close(self):
        '''
        关闭游标和数据库
        '''
        self.cursor.close()
        self.conn.close()

    @staticmethod
    def clear_script(sql_script):
        '''
        清洗脚本--注释,生成sql语句
        '''
        with open(sql_script, encoding='utf-8') as f:
            
            sql_ = ''
            lines = f.readlines()
            for line in lines:
                if line.isspace():
                    continue
                elif line.startswith('--'):
                    continue
                else:
                    rep_str = re.search('--.*', line)
                    if rep_str:
                        line = line.replace(rep_str.group(),'')                 
                    sql_ += ' ' + line.strip()

        return sql_

    def execute_script(self, sql_script):
        '''
        执行脚本
        '''
        for sql in self.clear_script(sql_script).split(';'):
            if sql:
                self.cursor.execute(sql)
        self.conn.commit()


    def execute_all_script(self):

        '''
        获取build_db文件中所有sql脚本并执行
        '''

        scripts = glob.glob('build_db/*.sql')

        for script in scripts:

            self.execute_script(script)








    
if __name__ == '__main__':

    pass
    